package com.kunbo.cn.service.impl.core.sale;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.JdbcTemplate;
import com.kunbo.cn.dao.core.sale.OrderCarryOverDao;
import com.kunbo.cn.entity.core.sale.OrderCarryOver;
import com.kunbo.cn.entity.core.vo.SalesOrders;
import com.kunbo.cn.service.core.sale.OrderCarryOverService;
import org.springframework.transaction.annotation.Transactional;
@Service
public class OrderCarryOverServiceImpl implements OrderCarryOverService {
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	@Autowired
	private OrderCarryOverDao dao;
	@Autowired
	private JdbcTemplate jdbc;
	@Autowired
    private JdbcTemplate jdbcTemplate;
	@Override
	public void save(OrderCarryOver carryOver) {
		carryOver.setCreateTime(sdf.format(new Date()));
		dao.save(carryOver);
		
	}
	@Override
	public void update(OrderCarryOver carryOver) {
		dao.save(carryOver);
	}
	
	@Override
	public void updateExamineState(OrderCarryOver carryOver) {
		 carryOver.setState(1);
		dao.save(carryOver);
	}
	
	@Override
	public void delete(String id) {
		dao.delete(id);
	}
	
	@Override
	public OrderCarryOver findById(String id) {
		return dao.findOne(id);
	}
	
	@Override
	public void updateSate(OrderCarryOver carryOver) {
		dao.save(carryOver);
	}
	/**
	 * 分页条件列表查询
	 */
	@Override
	public Page<OrderCarryOver> findOrderCarryOver(String noticeCode, Integer pageNo, Integer pageSize) {
		Pageable pageable = new PageRequest(pageNo-1, pageSize);
        Specification<OrderCarryOver> confusion=new Specification<OrderCarryOver>() {
            @Override
            public Predicate toPredicate(Root<OrderCarryOver> root, CriteriaQuery<?> query,
                    CriteriaBuilder criteriaBuilder) {
            	List<Predicate> predicates = new ArrayList<>();
            	if(!"".equals(noticeCode)) {
            		predicates.add(criteriaBuilder.like(root.get("noticeCode"), "%"+noticeCode+"%"));
            	}
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        return dao.findAll(confusion, pageable);
	}
	/**
	 * 获取销售订单下拉选
	 */
	@Override
	public List<SalesOrders> getSalesOrder(HttpServletRequest request, HttpServletResponse response) throws SQLException {
		String sql = "";
		sql +="select\r\n" ; 
		sql +=" *  FROM business_sale_order_manage d where d.state<>5 and d.state<>6 \r\n" ; 
		Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        ResultSet rs = stmt.executeQuery(sql);
        List<SalesOrders> so = new ArrayList<SalesOrders>();
        while(rs.next()) {
        	  SalesOrders sa=new  SalesOrders();
        	sa.setId(rs.getString("uuid"));
        	sa.setMaterial(rs.getString("materials"));
        	sa.setOrderCode(rs.getString("sale_order_code"));
        	sa.setLowerAmount(rs.getFloat("contr_margin"));
        	so.add(sa);
        }
        rs.close();
        stmt.close();
        return so;
	}
	/**
	 *
	 * 1.修改销售订单余量结转后订单余量(包括调量订单,调入订单余量)
	 * 2.先判断调量，调入订单号是否为空；
	 * 3.查询两个订单号两种材料是否一致
	 * 4.查询调量订单余量，查询调入订单余量，判断调量订单余量是否大于调整量
	 * 5.执行调量
	 * 6.先执行调量再保存调转订单
	 * 7.保证单一修改事务的回滚;
	 * 
	 */
	@Transactional
	@Override
	//@Transactional
	public int updateOrderNum(String OldOrder,String newOrder) throws SQLException {
		
		//String OldOrder = request.getParameter("oldOrder"); //调量订单
		//String newOrder = request.getParameter("newOrder"); //调入订单
		int n=0; int i=0;int n1=0;
		  if((OldOrder!=null && !OldOrder.isEmpty())&&(newOrder!=null && !newOrder.isEmpty())) {
				//1.修改调量订单(先查询余量)
				String sql="";
				sql +="select\r\n" ; 
				sql +="d.contr_margin,d.materials\r\n" ;
				sql +="FROM business_sale_order_manage d\r\n" ; 
				sql +="WHERE  d.sale_order_code='"+OldOrder+"'\r\n";
				//2.修改调入订单(先查询余量)
				String sql1="";
				sql1 +="select\r\n" ; 
				sql1 +="d.contr_margin,d.materials \r\n" ;
				sql1 +="FROM business_sale_order_manage d\r\n" ; 
				sql1 +="WHERE  d.sale_order_code='"+newOrder+"'\r\n";
				List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
				Float t = (Float) list.get(0).get("contr_margin");
				String m= (String) list.get(0).get("materials");
				Float lowerAmount=0f;
				
				List<Map<String,Object>> list2 = jdbcTemplate.queryForList(sql1);
				Float t2 = (Float) list2.get(0).get("contr_margin");
				String m2= (String) list.get(0).get("materials");
				Float lowerAmount2=t2+t;
				//如果两个订单的材料一致才可以执行修改订单余量
					if(m==m2) {
				String sql3 = "UPDATE \r\n" +
		        "business_sale_order_manage d \r\n"+
		        " set d.contr_margin="+lowerAmount+"\r\n "+
		        " WHERE  d.sale_order_code='"+OldOrder+"'";
				 n=jdbcTemplate.update(sql3);
				String sql4 = "UPDATE \r\n" +
				"business_sale_order_manage d \r\n"+
				" set d.contr_margin="+lowerAmount2+"\r\n "+
				" WHERE  d.sale_order_code='"+newOrder+"'";
				n1=jdbcTemplate.update(sql4);
		         i=n+n1;
		         //返回2表示成功
				}else {i=0;}
		  }else {
			  i=0;
		  }
		return i;
		 
	}
	
	/**
	 * 添加运单后修改订单余量
	 */
	@Override
	public int updateOrderMargin(HttpServletRequest request, HttpServletResponse response) throws SQLException {
		int n=0;
		String OrderCode = request.getParameter("orderCode"); //订单编号
		String train_number = request.getParameter("transTotal");//运输量
		if(OrderCode!=null&&!OrderCode.isEmpty()) {
		String sql="";
		sql +="select\r\n" ; 
		sql +="d.contr_margin  \r\n";
		sql +="FROM business_sale_order_manage  d\r\n" ; 
		sql +="WHERE  d.sale_order_code='"+OrderCode+"'\r\n";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		Float t = (Float) list.get(0).get("contr_margin");
		Float t1=Float.parseFloat(train_number);
		Float lowerAmount=t-t1;
		
		if(lowerAmount>=t1) {
		String sql3 = "UPDATE \r\n" +
		        "business_sale_order_manage d \r\n"+
		        " set d.contr_margin="+lowerAmount+"\r\n "+
		        " WHERE  d.sale_order_code='"+OrderCode+"'";
				
				 n=jdbcTemplate.update(sql3);
		}else {
			n=0;
		     }
		}else {
			n=0;
		}
		return n;
	}
	/**
	 * 编辑运单后修改订单余量
	 */
	@Override
	public int editOrderMargin(HttpServletRequest request, HttpServletResponse response) throws SQLException {
		int n=0;
		String OrderCode = request.getParameter("orderCode"); //订单编号
		String train_number = request.getParameter("transTotal");//运输量
		String train_number1 = request.getParameter("oldTransTotal");//编辑前的运输量
		if(OrderCode!=null&&!OrderCode.isEmpty()) {
		String sql="";
		sql +="select\r\n" ; 
		sql +="d.contr_margin  \r\n" ;
		sql +="FROM business_sale_order_manage d\r\n" ; 
		sql +="WHERE  d.sale_order_code='"+OrderCode+"'\r\n";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		Float t = (Float) list.get(0).get("contr_margin");
		Float num=Float.parseFloat(train_number1);
		Float tn =Float.parseFloat(train_number);
		if(t>=tn) {
		Float lowerAmounts=num-tn;
		Float number =t+lowerAmounts;
		String sql3 = "UPDATE \r\n" +
		        "business_sale_order_manage d \r\n"+
		        " set d.contr_margin="+number+"\r\n "+
		        " WHERE  d.sale_order_code='"+OrderCode+"'";
				  n=jdbcTemplate.update(sql3);
		}else {
			n=0;
		    }
		}else {
			n=0;
		}
		return n;
	}
}
